package com.logica.mdm.webservices.dao.impl;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import oracle.jdbc.OracleResultSet;
import oracle.jdbc.OracleTypes;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import com.logica.mdm.webservices.dao.SearchPartyDao;
import com.logica.mdm.webservices.dao.beans.PartyByMdmIdResultBean;
import com.logica.mdm.webservices.dao.beans.PartyByPhoneNumberResultBean;
import com.logica.mdm.webservices.dao.beans.PartyResultBean;
import com.logica.mdm.webservices.messages.SearchPartyRequest;

/**
 * Implementation using NamedParameterJdbcTemplate
 */
@Repository
public class JdbcSearchPartyDaoImpl implements SearchPartyDao{

	private NamedParameterJdbcTemplate jdbcTemplate;
    //private SimpleJdbcCall procReadAllPartyResul;
    private DataSource dataSource;
    private Connection   connexion                   = null;
    
	@Autowired
	public void setDataSource(DataSource dataSource) {
	    this.jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
	    this.dataSource = dataSource;
	}
	
			
	/**
	 * {@inheritDoc}
	 */
	public List<PartyResultBean> searchParty(String sqlQuery, Map<String,?> sqlParams) {
		return this.jdbcTemplate.query(sqlQuery, sqlParams, new PartyMapper());
	}
	
	/**
	* {@inheritDoc}
	*/
	public List<PartyResultBean> searchParty(SearchPartyRequest searchParams)throws Exception {
	
		CallableStatement cstmt = null;
		OracleResultSet           rset  = null;
		List<PartyResultBean> listPartyResultBean = new ArrayList<PartyResultBean>();
		
		try {
			this.connexion = this.dataSource.getConnection();
		    
	    	connexion.setAutoCommit(false); // Explicit transaction handling
	        cstmt = connexion.prepareCall(
	            "{call PKG_SEARCHPARTY.PRC_SEARCH (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}"
	        );
	        cstmt.registerOutParameter(1, OracleTypes.CURSOR);
	        cstmt.setString(2, searchParams.getCityName()); 
	        cstmt.setString(3, searchParams.getStreetName());
	        cstmt.setString(4, searchParams.getPostalCode());
	        cstmt.setString(5, searchParams.getCountryCode());
	        cstmt.setString(6, searchParams.getContractNumber());
	        cstmt.setString(7, searchParams.getContractOffice());
	        cstmt.setString(8, searchParams.getFirstName());
	        cstmt.setString(9, searchParams.getLastName());
	        cstmt.setString(10, searchParams.getGender());
	        cstmt.setString(11, searchParams.getPartyType());
	        cstmt.setString(12, searchParams.getLegalForm());
	        cstmt.setString(13, searchParams.getRefClient());
	        cstmt.setString(14, searchParams.getMdmId());
	        cstmt.setString(15, searchParams.getCrmId());
	        cstmt.setString(16, searchParams.getPhoneNumber());
	        cstmt.setString(17, searchParams.getElectronicAddress());
	        
	        if (searchParams.getRecordsToReturn() != null && searchParams.getRecordsToReturn() <= 999)
	        	cstmt.setInt(18, searchParams.getRecordsToReturn());
	        else
	        	cstmt.setInt(18, 999);
	        
	        cstmt.execute();
	
	        rset = (OracleResultSet         ) cstmt.getObject(1);
	        
	        while (rset.next()) {
	            
	            PartyResultBean partyResultBean = new PartyResultBean();
	            partyResultBean.setRowidObject(rset.getString("rowidObject"));
	            partyResultBean.setPartyType(rset.getString("partyType"));
	            partyResultBean.setPartyTypeDisp(rset.getString("partyTypeDisp"));
	            partyResultBean.setGenderCd(rset.getString("genderCd"));
	            partyResultBean.setGenderDisp(rset.getString("genderDisp"));
	            partyResultBean.setLegalFormCd(rset.getString("legalFormCd"));
	            partyResultBean.setLibelleLegalForm(rset.getString("libelleLegalForm"));
	            partyResultBean.setNamePrefixCd(rset.getString("namePrefixCd"));
	            partyResultBean.setLastName(rset.getString("lastName"));
	            partyResultBean.setFirstName(rset.getString("firstName"));
	            partyResultBean.setBirthdate(rset.getDate("birthdate"));
	            partyResultBean.setUnitNumber(BigInteger.valueOf(rset.getInt("unitNumber")));
	            partyResultBean.setFloorNumber(BigInteger.valueOf(rset.getInt("floorNumber")));
	            partyResultBean.setStreetNumber(rset.getString("streetNumber"));
	            partyResultBean.setStreetType(rset.getString("streetType"));
	            partyResultBean.setStreetName(rset.getString("streetName"));
	            partyResultBean.setAddressLine1(rset.getString("addressLine1"));
	            partyResultBean.setAddressLine2(rset.getString("addressLine2"));
	            partyResultBean.setAddressLine3(rset.getString("addressLine3"));
	            partyResultBean.setAddressLine5(rset.getString("addressLine5"));
	            partyResultBean.setPostalCd(rset.getString("postalCd"));
	            partyResultBean.setCityName(rset.getString("cityName"));
	            partyResultBean.setCountryName(rset.getString("countryName"));
	            partyResultBean.setCountryCode(rset.getString("countryCode"));
	            partyResultBean.setHexacleVoie(rset.getString("hexacleVoie"));
	            partyResultBean.setHexacleLocalite(rset.getString("hexacleLocalite"));
	            partyResultBean.setHomePhoneNumber(rset.getString("homePhoneNumber"));
	            partyResultBean.setCellPhoneNumber(rset.getString("cellPhoneNumber"));
	            partyResultBean.setWorkPhoneNumber(rset.getString("workPhoneNumber"));
	            partyResultBean.setFaxPhoneNumber(rset.getString("faxPhoneNumber"));
	            partyResultBean.setElectronicAddress(rset.getString("electronicAddress"));
	            partyResultBean.setTypeContrat(rset.getString("typeContrat"));
	            partyResultBean.setStatutContrat(rset.getString("statutContrat"));
	            partyResultBean.setNumeroContrat(rset.getString("numeroContrat"));
	            partyResultBean.setContractOffice(rset.getString("contractOffice"));
	            partyResultBean.setDateDebutValiditeContrat(rset.getString("dateDebutValiditeContrat"));
	            partyResultBean.setDateFinValiditeContrat(rset.getString("dateFinValiditeContrat"));
	            partyResultBean.setFlagVip(rset.getString("flagVip"));
	            partyResultBean.setFlagPartenaire(rset.getString("flagPartenaire"));
	            partyResultBean.setDebranchementSigeo(rset.getString("debranchementSigeo"));
	            partyResultBean.setCrmId(rset.getString("crmId"));
	            partyResultBean.setLogementId(rset.getString("logementId"));
	            partyResultBean.setServiceId(rset.getString("serviceId"));
	            
	            listPartyResultBean.add(partyResultBean);
	        }
	        System.out.println();
	
	        connexion.commit(); // Commit transaction
	        
	        cstmt.close();
			connexion.close();
		    
		} catch (SQLException e1) {
			e1.printStackTrace();
			throw (new Exception("JdbcSearcPartyDaoImpl.searchParty:" + e1.getMessage())) ;
		}
		return listPartyResultBean;
	}
			
			
	/**
	 * {@inheritDoc}
	 */
	public List<PartyByPhoneNumberResultBean> searchPartyByPhoneNumber(String sqlQuery, Map<String,?> sqlParams) {
		return this.jdbcTemplate.query(sqlQuery, sqlParams, new PartyByPhoneNumberMapper());
	}
	
	/**
	 * {@inheritDoc}
	 */
	public PartyByMdmIdResultBean searchPartyByMdmId(String sqlQuery, Map<String,?> sqlParams) {
		return this.jdbcTemplate.queryForObject(sqlQuery, sqlParams, new PartyByMdmIdMapper());
	}
	
	/**
	 * Inner class defining the mapping between the database and PartyResultBean
	 */
	private static final class PartyMapper implements RowMapper<PartyResultBean> {
		public PartyResultBean mapRow(ResultSet rs, int rowNum) throws SQLException {
			PartyResultBean party = new PartyResultBean();
			party.setRowidObject(rs.getString("ROWID_OBJECT"));
			party.setPartyType(rs.getString("PARTY_TYPE"));
			party.setPartyTypeDisp(rs.getString("PARTY_TYPE_DISP"));
			party.setGenderCd(rs.getString("GENDER_CD"));
			party.setGenderDisp(rs.getString("GENDER_DISP"));
			party.setLegalFormCd(rs.getString("LEGAL_FORM_CD"));
			party.setLibelleLegalForm(rs.getString("LIBELLE_LEGAL_FORM"));
			party.setNamePrefixCd(rs.getString("NAME_PREFIX_CD"));
			party.setLastName(rs.getString("LAST_NAME"));
			party.setFirstName(rs.getString("FIRST_NAME"));
			party.setBirthdate(rs.getTimestamp("BIRTHDATE"));
			BigDecimal unitNumber = rs.getBigDecimal("UNIT_NUMBER");
			if (unitNumber != null) {
				party.setUnitNumber(unitNumber.toBigInteger());
			}
			BigDecimal floorNumber = rs.getBigDecimal("FLOOR_NUMBER");
			if (floorNumber != null) {
				party.setFloorNumber(floorNumber.toBigInteger());
			}
			party.setStreetNumber(rs.getString("STREET_NUMBER"));
			party.setStreetType(rs.getString("STREET_TYPE"));
			party.setStreetName(rs.getString("STREET_NAME"));
			party.setAddressLine1(rs.getString("ADDRESS_LINE_1"));
			party.setAddressLine2(rs.getString("ADDRESS_LINE_2"));
			party.setAddressLine3(rs.getString("ADDRESS_LINE_3"));
			party.setAddressLine5(rs.getString("ADDRESS_LINE_5"));
			party.setPostalCd(rs.getString("POSTAL_CD"));
			party.setCityName(rs.getString("CITY_NAME"));
			party.setCountryName(rs.getString("COUNTRY_NAME"));
			party.setCountryCode(rs.getString("COUNTRY_CODE"));
			party.setHexacleVoie(rs.getString("HEXACLE_VOIE"));
			party.setHexacleLocalite(rs.getString("HEXACLE_LOCALITE"));
			party.setHomePhoneNumber(rs.getString("HOME_PHONE_NUMBER"));
			party.setCellPhoneNumber(rs.getString("CELL_PHONE_NUMBER"));
			party.setWorkPhoneNumber(rs.getString("WORK_PHONE_NUMBER"));
			party.setFaxPhoneNumber(rs.getString("FAX_PHONE_NUMBER"));
//			party.setElectronicAddress(rs.getString("ELECTRONIC_ADDRESS"));
//			party.setTypeContrat(rs.getString("TYPE_CONTRAT"));
//			party.setStatutContrat(rs.getString("STATUT_CONTRAT"));
//			party.setNumeroContrat(rs.getString("NUMERO_CONTRAT"));
//			party.setContractOffice(rs.getString("CONTRACT_OFFICE"));
//			party.setDateDebutValiditeContrat(rs.getString("DATE_DEBUT_VALIDITE_CONTRAT"));
//			party.setDateFinValiditeContrat(rs.getString("DATE_FIN_VALIDITE_CONTRAT"));
			party.setFlagVip(rs.getString("FLAG_VIP"));
			party.setFlagPartenaire(rs.getString("FLAG_PARTENAIRE"));
			party.setDebranchementSigeo(rs.getString("DEBRANCHEMENT_SIGEO"));
//			party.setCrmId(rs.getString("CRM_ID"));
//			party.setLogementId(rs.getString("LOGEMENT_ID"));
//			party.setServiceId(rs.getString("SERVICE_ID"));
	        return party;
	    }
	}
	
	/**
	 * Inner class defining the mapping between the database and PartyByPhoneNumberResultBean
	 */
	private static final class PartyByPhoneNumberMapper implements RowMapper<PartyByPhoneNumberResultBean> {
		public PartyByPhoneNumberResultBean mapRow(ResultSet rs, int rowNum) throws SQLException {
			PartyByPhoneNumberResultBean party = new PartyByPhoneNumberResultBean();
			party.setPartyId(rs.getString("PARTY_ID"));
	        return party;
	    }        
	}
	
	/**
	 * Inner class defining the mapping between the database and PartyByMdmIResultBean
	 */
	private static final class PartyByMdmIdMapper implements RowMapper<PartyByMdmIdResultBean> {
		public PartyByMdmIdResultBean mapRow(ResultSet rs, int rowNum) throws SQLException {
			PartyByMdmIdResultBean party = new PartyByMdmIdResultBean();
			party.setRowidObject(rs.getString("ROWID_OBJECT"));
			party.setNamePrefixCd(rs.getString("NAME_PREFIX_CD"));
			party.setNamePrefixDisp(rs.getString("NAME_PREFIX_DISP"));
			party.setLegalFormCd(rs.getString("LEGAL_FORM_CD"));
			party.setLibelleLegalForm(rs.getString("LIBELLE_LEGAL_FORM"));
			party.setGenderCd(rs.getString("GENDER_CD"));
			party.setGenderDisp(rs.getString("GENDER_DISP"));
			party.setLastName(rs.getString("LAST_NAME"));
			party.setFirstName(rs.getString("FIRST_NAME"));
			party.setTaxId(rs.getString("TAX_ID"));
			party.setBirthdate(rs.getTimestamp("BIRTHDATE"));
			BigDecimal numberOfChildren = rs.getBigDecimal("NUMBER_OF_CHILDREN");
			if (numberOfChildren != null) {
				party.setNumberOfChildren(numberOfChildren.toBigInteger());
			}
			party.setMaritalStatus(rs.getString("MARITAL_STATUS"));
			party.setMaritalStatusTypeDisp(rs.getString("MARITAL_STATUS_TYPE_DISP"));
			party.setSocialCategory(rs.getString("SOCIAL_CATEGORY"));
			party.setSocialCategoryLabel(rs.getString("SOCIAL_CATEGORY_LABEL"));
			party.setHomePhoneNumber(rs.getString("HOME_PHONE_NUMBER"));
			party.setCellPhoneNumber(rs.getString("CELL_PHONE_NUMBER"));
			party.setWorkPhoneNumber(rs.getString("WORK_PHONE_NUMBER"));
			party.setFaxPhoneNumber(rs.getString("FAX_PHONE_NUMBER"));
			party.setPartyType(rs.getString("PARTY_TYPE"));
			party.setPartyTypeDisp(rs.getString("PARTY_TYPE_DISP"));
			party.setDebranchementSigeo(rs.getString("DEBRANCHEMENT_SIGEO"));
			return party;
		}
	}
}
